﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IHISCETeim.Library;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using IHISCETeim.Model;

namespace IHISCETeim.DAL
{
    public class SLogDAL
    {
        /// <summary>
        /// 日志系统连接字符串
        /// </summary>
        protected static string ConnectionString
        {
            get
            {
                return "LogSysConnectionString";
            }
        }

        public static int Add(SLogInfo obj)
        {
            Database DB = DatabaseFactory.CreateDatabase(ConnectionString);

            DbCommand dbCommand = DB.GetStoredProcCommand("SLog_Add", obj.LogType, obj.DWZRef, obj.RealName, obj.OpType, obj.OpOldObj, obj.OpNewObj);
            return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
        }
        public static IDataReader GetList(int id)
	    {
            Database DB = DatabaseFactory.CreateDatabase(ConnectionString);		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("SLog_GetList", id);
            return DB.ExecuteReader(dbCommand);
	    }
        public static DataTable GetPagination(string SqlWhere, int PageIndex, int PageSize, string Sort, ref int TotalCount)
	    {
            Database DB = DatabaseFactory.CreateDatabase(ConnectionString);		
		
            DbCommand dbCommand = DB.GetStoredProcCommand("SLog_GetPagination");
            DB.AddInParameter(dbCommand, "SqlWhere", DbType.String,SqlWhere);
            DB.AddInParameter(dbCommand, "PageIndex", DbType.Int32,PageIndex);
            DB.AddInParameter(dbCommand, "PageSize", DbType.Int32,PageSize);
            DB.AddInParameter(dbCommand, "Sort", DbType.String,Sort);
            DB.AddOutParameter(dbCommand, "TotalCount", DbType.Int32,8);
			DataTable dt=  DB.ExecuteDataSet(dbCommand).Tables[0];
				
            TotalCount=Convert.ToInt32( DB.GetParameterValue(dbCommand,"TotalCount"));
            return dt;
	    }


        public static int SPageExecTime_Add(string URL, string PathAndQuery, DateTime StartDate, DateTime EndDate, string RealName)
        {
            try
            {
                Database DB = DatabaseFactory.CreateDatabase(ConnectionString);

                DbCommand dbCommand = DB.GetStoredProcCommand("SPageExecTime_Add", URL, PathAndQuery, StartDate, EndDate, RealName);
                return Convert.ToInt32(DB.ExecuteScalar(dbCommand));
            }
            catch (Exception)
            {
                return 0;
                //throw;
            }
           
        }

        public static bool 清空三井原始逾期表()
        {

            try
            {
                Database DB = DatabaseFactory.CreateDatabase(ConnectionString);

                DbCommand dbCommand = DB.GetSqlStringCommand("DELETE FROM [三井原始逾期表]");
                DB.ExecuteScalar(dbCommand);

                return true;
            }
            catch (Exception)
            {
                return false;
                //throw;
            }
            return true;
        }

        public static string 导入三井原始逾期表(DataTable dt)
        {
            Database DB = DatabaseFactory.CreateDatabase(ConnectionString);
            using (DbConnection conn = DB.CreateConnection())
            {
                //打开连接
                conn.Open();
                //创建事务
                int rowindex = 1;
                DbTransaction trans = conn.BeginTransaction();
                try
                {
                    string 代理店等 = "";
                    string 客户名 = "";
                    string 合同编号 = "";
                    int 期数 = 0;
                    DateTime 预定支付日 = DateTime.Now;
                    decimal 预定回收金额 = 0m;
                    decimal 尚未支付金额 = 0m;
                    decimal 已回收部分租金 = 0m;

                    
                    foreach (DataRow dr in dt.Rows)
                    {
                        rowindex++;

                        代理店等 = dr["代理店等"].ToString();
                        客户名 = dr["客户名"].ToString();
                        合同编号 = dr["合同编号"].ToString();
                        期数 =Convert.ToInt32( dr["期数"].ToString());
                        预定支付日 = Convert.ToDateTime(dr["预定支付日"]);
                        预定回收金额 = Convert.ToDecimal(dr["预定回收金额"]);
                        尚未支付金额 = Convert.ToDecimal(dr["尚未支付金额"]);
                        已回收部分租金 = Convert.ToDecimal(dr["已回收部分租金"]);


                        DbCommand dbCommand = DB.GetSqlStringCommand("INSERT dbo.三井原始逾期表(代理店等,客户名,合同编号,期数,预定支付日,预定回收金额,尚未支付金额,已回收部分租金)VALUES  (@代理店等,@客户名,@合同编号,@期数,@预定支付日,@预定回收金额,@尚未支付金额,@已回收部分租金)");
                        DB.AddInParameter(dbCommand, "代理店等", DbType.String, 代理店等);
                        DB.AddInParameter(dbCommand, "客户名", DbType.String, 客户名);
                        DB.AddInParameter(dbCommand, "合同编号", DbType.String, 合同编号);
                        DB.AddInParameter(dbCommand, "期数", DbType.Int32, 期数);
                        DB.AddInParameter(dbCommand, "预定支付日", DbType.DateTime, 预定支付日);
                        DB.AddInParameter(dbCommand, "预定回收金额", DbType.Decimal, 预定回收金额);
                        DB.AddInParameter(dbCommand, "尚未支付金额", DbType.Decimal, 尚未支付金额);
                        DB.AddInParameter(dbCommand, "已回收部分租金", DbType.Decimal, 已回收部分租金);

                        DB.ExecuteNonQuery(dbCommand, trans);

                    }

                    trans.Commit();
                    return "";
                }
                catch(Exception ex)
                {
                    //发生异常，事务回滚
                    trans.Rollback();
                    return string.Format("第{0}行导入出错,{1}",rowindex,ex.Message);
                    //return exc.Message;
                }
                finally
                {
                    //关闭连接
                    conn.Close();
                }
            }
        }
    }
}
